Search engine won"t page properly

The website I have been working on (as time permits) reports over
15,000 woods. With that much data, an internal search engine has
become mandatory for users to find what they are looking for. Almost
all the pages are dynamically formed using PHP and data stored in
MySQL files.

Therefore, a search engine that works on hard coded pages would be
totally unusable. I needed one that can search through MySQL tables.
I found a decent template for such a search engine at:
http://www.designplace.org/scripts.php?page=3D1&c_id=3D25
The original template works on searching one column in one table and
reporting only from that one column. The part of the data that by far
most users will wish to search is the species table. I wanted the search
report to show data keyed by one column but displaying multiple
columns --- so I knew I had some additional coding to do..
I
A while back I actually got a copy working for a while, broke it trying to
change it over to working with multiple column choices and then didn't
have a copy left of the code that worked..

I have come a fair way to getting it to work but it won't page properly.
It has a parameter ($limit, presently set to 10 records) to control how
many records can be printed per page). It prints one page but gets
stuck. It has "<>" links for users to go backward or
forward in pages.

I have put in hours heavily adding internal documentation, studying the
logic till I understood most of it and plastering ECHO statements on
parameters to understand what is happening.

I think I know now what is wrong. $s is the record counter. Once its
value in multiples has a run over for the current page, a new page is
created by incrementing the record counter as the low end record for
the next page for forward travel and decrementing=20
for going backwards. The next page (or prior=20
page) is printed using $PHP_SELF statements
to run the same query again but in the prior or next page.

It took a lot of study , but as far as I can tell (..... and prove me wrong=
if
I am), when the program is turned back on itself=20
(using $PHP_SELF statements, whether for=20
incrementing or decrementing pages), the
value of $s near the top of the program gets lost. It has no chance to
accumulate (or reduce for backward travel). Therefore the program
gets locked into showing only the first page.

To test this, I hard set $s to a higher value, and ran a query. It
responded as it should, now reporting higher record numbers. As far as
I can see, repairing the paging problem seems to hinge on allowing
$s to carry over to be usable at the top of the program when
$PHP_SELF turns the program on itself.

What I am not familiar with is what the best way is to do that. What do
you suggest? Including code example if possible would be helpful?
If you also happen to spot any other serious bugs, please also let me
know. I am largely confident once $s can carry forward on each
program reiteration, the search engine will work as it should, good
paging included.

Sharing the Near Future
I have already done quite a bit of work on code (that works) that allows
users to choose what column to search on (eg. common names,
botanical names, country of origin, etc.). Once this paging problem is
overcome, It should be fairly easy to expand the search engine to work
on multiple search categories (ie. columns). By that point, it will make
the knowledge base website immensely more powerful and useful
for users to get versatile access to all its stored data. I really look
forward to that!

I will include all the code for this page below. I am also keeping the
large number of debug statements in and active and showing on the
output in a browser when the program is run --- so you can follow what
values parameters have at each step. The search page can be
directly viewed at:
http://www.prowebcanada.com/taxa/commonname_search.php?query string=3DOak&Sub=
mit=3DSearch

Much thanks. Your help makes it possible to go forward where
otherwise I would have a hard or impassible time.

Bill Mudry
Mississauga, Ontario

==================== =====3D=
==================== =====3D=
==
[CODE]















//$s =3D 14; hard coded as a debug statement
$S=3D @$_GET['$s'] ;
Echo "\$s on line 69 is - $s
"; //debug statement
?>

Simple Common Name Search

Please enter a search string...

We dont seem to have a search parameter!

Search Results

Sorry, your search: "" .=20
$querystring. "" returned zero results

You searched for: ""=20
.. $querystring . ""

Search Results

RE: Search engine won"t page properly

Bill,

That's a lot of reading ... Anyway, this seems to be a 'general' PHP
question. If I understood you correctly, you're having problem getting PHP
to page the SQL results? If so, look the below code logic and adapt as
necessary:

$numOfResults = 10; // change this to your need
if( !empty($_GET['page']) && intval($_GET['page']) > 1 ) $currentPage =
intval($_GET['page']) - 1; // change the $_GET['page'] accordingly
elseif (!empty($_GET['page']) && strtolower(trim($_GET['page'])) == 'all')
$currentPage = 'all';
else $currentPage = 0;

$sqlQuery = 'SELECT * FROM my_table ';

If( $currentPage != 'all' ) $sqlQuery .= ' LIMIT
'.$currentPage*$numOfResults.', '.$numOfResults; // see [1]

$result = mysql_query($sqlQuery);

Would give you the following:

?page=all returns all results
?page=1 or ?page=0 or ?page= or ? yields 1st batch $numOfResults ( 1 to 10
)
?page=2 yields 2nd batch $numOfResults ( 11 to 20 )
?page=3 yields 3rd batch $numOfResults ( 21 to 30 )
?page=4 yields 4th batch $numOfResults ( 31 to 40 )

Etc....

Regards,
Tommy

[1] http://dev.mysql.com/doc/refman/5.1/en/select.html


--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Search engine won"t page properly

--=====================_1928409572==.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed

At 12:11 AM 04/02/2011, Tommy Pham wrote:
>Bill,
>
>That's a lot of reading ... Anyway, this seems to be a 'general' PHP
>question. If I understood you correctly, you're having problem getting PHP
>to page the SQL results? If so, look the below code logic and adapt as
>necessary:
>
>$numOfResults = 10; // change this to your need
>if( !empty($_GET['page']) && intval($_GET['page']) > 1 ) $currentPage =
>intval($_GET['page']) - 1; // change the $_GET['page'] accordingly
>elseif (!empty($_GET['page']) && strtolower(trim($_GET['page'])) == 'all')
>$currentPage = 'all';
>else $currentPage = 0;
>
>$sqlQuery = 'SELECT * FROM my_table ';
>
>If( $currentPage != 'all' ) $sqlQuery .= ' LIMIT
>'.$currentPage*$numOfResults.', '.$numOfResults; // see [1]
>
>$result = mysql_query($sqlQuery);
>
>Would give you the following:
>
>?page=all returns all results
>?page=1 or ?page=0 or ?page= or ? yields 1st batch $numOfResults ( 1 to 10
>)
>?page=2 yields 2nd batch $numOfResults ( 11 to 20 )
>?page=3 yields 3rd batch $numOfResults ( 21 to 30 )
>?page=4 yields 4th batch $numOfResults ( 31 to 40 )
>
>Etc....
>
>Regards,
>Tommy

Perhaps interesting code but when I said including code would help, I
was referring to
code for the best way of preserving the record counter, $s, when the
program the program
recycles back on itself on PHP_SELF. As mentioned, at present it does
not carry forward
the $s value so it can accumulate with the next page of records and
therefore gets stuck on
page one.

Thank you for your effort.

The general code is already there. In fact, if you were to take a
closer look, it is a fairly
sophisticated algorithm with a lot of checks and validations. It is
also very user friendly,
letting users click on "Next >>" to go ahead one page and "<< Prev"
to go backward one
page.

My testing shows that it should work well IF the value of the record
counter would only
transfer on restarting the page. I will try using SESSION today to
see if it will do the job.

Bill Mudry
MIssissauga


>[1] http://dev.mysql.com/doc/refman/5.1/en/select.html

--=====================_1928409572==.ALT--

RE: Search engine won"t page properly

Previously,

> [CODE]



>
>
> > //$s = 14; hard coded as a debug statement
> $S= @$_GET['$s'] ;

With the above, PHP is looking for the GET parameter name $s, not the value
of $s as the the GET parameter name. If you want the latter, change it to
$_GET[$s]. IIRC, PHP is not case specific, not like Java and C#.

> Echo "\$s on line 69 is - $s
"; //debug statement
> ?>



>
> [/CODE]


On Fri, Feb 4, 2011 at 8:05 AM, Bill Mudry wrote:
> At 12:11 AM 04/02/2011, Tommy Pham wrote:
>
> Bill,
>
> That's a lot of reading ... Anyway, this seems to be a 'general' PHP
> question. If I understood you correctly, you're having problem getting
PHP
> to page the SQL results? If so, look the below code logic and adapt as
> necessary:
>
> $numOfResults = 10; // change this to your need
> if( !empty($_GET['page']) && intval($_GET['page']) > 1 ) $currentPage =
> intval($_GET['page']) - 1; // change the $_GET['page'] accordingly
> elseif (!empty($_GET['page']) && strtolower(trim($_GET['page'])) == 'all')
> $currentPage = 'all';
> else $currentPage = 0;
>
> $sqlQuery = 'SELECT * FROM my_table ';
>
> If( $currentPage != 'all' ) $sqlQuery .= ' LIMIT
> '.$currentPage*$numOfResults.', '.$numOfResults; // see [1]
>
> $result = mysql_query($sqlQuery);
>
> Would give you the following:
>
> ?page=all returns all results
> ?page=1 or ?page=0 or ?page= or ? yields 1st batch $numOfResults ( 1 to
10
> )
> ?page=2 yields 2nd batch $numOfResults ( 11 to 20 )
> ?page=3 yields 3rd batch $numOfResults ( 21 to 30 )
> ?page=4 yields 4th batch $numOfResults ( 31 to 40 )
>
> Etc....
>
> Regards,
> Tommy
>
> Perhaps interesting code but when I said including code would help, I was
> referring to
> code for the best way of preserving the record counter, $s, when the
program
> the program
> recycles back on itself on PHP_SELF. As mentioned, at present it does not
> carry forward
> the $s value so it can accumulate with the next page of records and
> therefore gets stuck on
> page one.
>
> Thank you for your effort.
>
> The general code is already there. In fact, if you were to take a closer
> look, it is a fairly
> sophisticated algorithm with a lot of checks and validations. It is also
> very user friendly,
> letting users click on "Next >>" to go ahead one page and "<< Prev" to go
> backward one
> page.
>

I think you're over complicating things. Paging doesn't depend complicated
code algorithm but rather simple math deductions. See the below revised
code.

> My testing shows that it should work well IF the value of the record
counter
> would only
> transfer on restarting the page. I will try using SESSION today to see if
it
> will do the job.
>
> Bill Mudry
> Mississauga


Revised code:

$sqlSearchCount = "SELECT COUNT(*) AS Total FROM species WHERE $searchfield
like \"%$querystring%\" order by '$searchfield'"; /* look into [1] */

$result = mysql_query( $sqlSearchCount );
$totalRows = mysql_fetch_assoc('Total');

$numOfResults = 10;

$maxPages = ceil( $totalRows / $numOfResults );

if( !empty($_GET['page']) )
{
if( strtolower(trim($_GET['page'])) == 'all' )
{
$currentPage = 'all';
}
else {
$currentPage = intval($_GET['page']);
if( $currentPage > $maxPages ) $currentPage = 1;
elseif ( $currentPage > 1 ) $currentPage--;
else $currentPage = 1;
}
}
else $currentPage = 0;

$sqlSearch = "SELECT * FROM species WHERE $searchfield like
\"%$querystring%\" order by '$searchfield'";

if( $currentPage != 'all' ) $sqlSearch .= ' LIMIT
'.($currentPage-1)*$numOfResults.', '.$numOfResults;

$result = mysql_query( $sqlSearch );
/* code to display $result */

if( $currentPage > 1 ) echo 'Previous';
if( $currentPage < $maxPages ) echo 'Next';
if( $totalRows > 0 ) echo 'Showing '.($currentPage - 1)*$numOfResults+1.' to
'.( $currentPage < $maxPages ) ? ( $currentPage )*$numOfResults : $totalRows
..' of total: '.$totalRows;

NOTE: The code is not tested, but rather quickly cranked out based on
logic.

Regards,
Tommy

[1] http://php.net/mysql_escape_string



--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Search engine won"t page properly

> -----Original Message-----
> From: Tommy Pham [mailto:tommyhp2@gmail.com]
> Sent: Friday, February 04, 2011 3:49 PM
> To: 'Bill Mudry'
> Cc: 'php-windows@lists.php.net'
> Subject: RE: [PHP-WIN] Search engine won't page properly
>
>
> Previously,
>
> > [CODE]
>
>
>
> >
> >
> > > > //$s = 14; hard coded as a debug statement
> > $S= @$_GET['$s'] ;
>
> With the above, PHP is looking for the GET parameter name $s, not the
> value of $s as the the GET parameter name. If you want the latter, change
it
> to $_GET[$s]. IIRC, PHP is not case specific, not like Java and C#.
>
> > Echo "\$s on line 69 is - $s
"; //debug statement
> > ?>
>
>
>
> >
> > [/CODE]
>
>
> On Fri, Feb 4, 2011 at 8:05 AM, Bill Mudry wrote:
> > At 12:11 AM 04/02/2011, Tommy Pham wrote:
> >
> > Bill,
> >
> > That's a lot of reading ... Anyway, this seems to be a 'general' PHP
> > question. If I understood you correctly, you're having problem
> > getting PHP to page the SQL results? If so, look the below code logic
> > and adapt as
> > necessary:
> >
> > $numOfResults = 10; // change this to your need if(
> > !empty($_GET['page']) && intval($_GET['page']) > 1 ) $currentPage =
> > intval($_GET['page']) - 1; // change the $_GET['page'] accordingly
> > elseif (!empty($_GET['page']) && strtolower(trim($_GET['page'])) ==
> > 'all') $currentPage = 'all'; else $currentPage = 0;
> >
> > $sqlQuery = 'SELECT * FROM my_table ';
> >
> > If( $currentPage != 'all' ) $sqlQuery .= ' LIMIT
> > '.$currentPage*$numOfResults.', '.$numOfResults; // see [1]
> >
> > $result = mysql_query($sqlQuery);
> >
> > Would give you the following:
> >
> > ?page=all returns all results
> > ?page=1 or ?page=0 or ?page= or ? yields 1st batch $numOfResults ( 1
> > to 10
> > )
> > ?page=2 yields 2nd batch $numOfResults ( 11 to 20 )
> > ?page=3 yields 3rd batch $numOfResults ( 21 to 30 )
> > ?page=4 yields 4th batch $numOfResults ( 31 to 40 )
> >
> > Etc....
> >
> > Regards,
> > Tommy
> >
> > Perhaps interesting code but when I said including code would help, I
> > was referring to code for the best way of preserving the record
> > counter, $s, when the program the program recycles back on itself on
> > PHP_SELF. As mentioned, at present it does not carry forward the $s
> > value so it can accumulate with the next page of records and therefore
> > gets stuck on page one.
> >
> > Thank you for your effort.
> >
> > The general code is already there. In fact, if you were to take a
> > closer look, it is a fairly sophisticated algorithm with a lot of
> > checks and validations. It is also very user friendly, letting users
> > click on "Next >>" to go ahead one page and "<< Prev" to go backward
> > one page.
> >
>
> I think you're over complicating things. Paging doesn't depend
complicated
> code algorithm but rather simple math deductions. See the below revised
> code.
>
> > My testing shows that it should work well IF the value of the record
> > counter would only transfer on restarting the page. I will try using
> > SESSION today to see if it will do the job.
> >
> > Bill Mudry
> > Mississauga
>
>
> Revised code:
>
> $sqlSearchCount = "SELECT COUNT(*) AS Total FROM species WHERE
> $searchfield like \"%$querystring%\" order by '$searchfield'"; /* look
into
> [1] */
>
> $result = mysql_query( $sqlSearchCount ); $totalRows =
> mysql_fetch_assoc('Total');
>
> $numOfResults = 10;
>
> $maxPages = ceil( $totalRows / $numOfResults );
>
> if( !empty($_GET['page']) )
> {
> if( strtolower(trim($_GET['page'])) == 'all' )
> {
> $currentPage = 'all';
> }
> else {
> $currentPage = intval($_GET['page']);
> if( $currentPage > $maxPages ) $currentPage = 1;
> elseif ( $currentPage > 1 ) $currentPage--;
> else $currentPage = 1;
> }
> }
> else $currentPage = 0;

Sorry, was in hurry to go run an errand didn't have time to review my
e-mail. That should be else $currentPage = 1;

>
> $sqlSearch = "SELECT * FROM species WHERE $searchfield like
> \"%$querystring%\" order by '$searchfield'";
>
> if( $currentPage != 'all' ) $sqlSearch .= ' LIMIT '.($currentPage-
> 1)*$numOfResults.', '.$numOfResults;
>
> $result = mysql_query( $sqlSearch );
> /* code to display $result */
>
> if( $currentPage > 1 ) echo 'Previous'; if( $currentPage < $maxPages ) echo ' > href="?page='.$currentPage + 1.'">Next'; if( $totalRows > 0 ) echo
> 'Showing '.($currentPage - 1)*$numOfResults+1.' to '.( $currentPage <
> $maxPages ) ? ( $currentPage )*$numOfResults : $totalRows .' of total:
> '.$totalRows;
>
> NOTE: The code is not tested, but rather quickly cranked out based on
logic.
>
> Regards,
> Tommy
>
> [1] http://php.net/mysql_escape_string



--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Search engine won"t page properly

> -----Original Message-----
> From: Tommy Pham [mailto:tommyhp2@gmail.com]
> Sent: Friday, February 04, 2011 4:21 PM
> To: 'Bill Mudry'
> Cc: 'php-windows@lists.php.net'
> Subject: RE: [PHP-WIN] Search engine won't page properly
>




> > $result = mysql_query( $sqlSearchCount ); $totalRows =
> > mysql_fetch_assoc('Total');

Argh... interruptions...

$row = mysql_fetch_assoc($result);
$totalRows = $row['Total'];

Anyway, you get the idea.

Happy coding,
Tommy


--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php